﻿ALTER function [dbo].[fc_T_LayCTPhieu]
(
	@id_DPNX int
)
returns table
as
	return(
		select				vt.VatTu,
							vt.MaKyHieu,
							dv.DVT,							                 
							t.Gia,
							p.SLKH,
							p.ThanhTien,
							n.KH_NguonVatTu,
							hd.SoHopDong ,
							t.SoTheKho,
							t.TangKeKhoang, 
							p.GhiChu
	from tblSLNX p inner join tblTon t on p.ID_MatHang = t.ID_MatHang
	inner join tblVatTu vt on t.ID_VatTu = vt.ID_VatTu
	inner join tblDVT dv on vt.ID_DVT = dv.ID_DVT
	left join tblNguonVatTu n on t.ID_NguonVatTu = n.ID_NguonVatTu
	left join tblHopDong hd on hd.ID_HopDong = t.ID_HopDong
	where p.ID_DPNX = @id_DPNX
	)
GO
--*********************************************************
--Người tạo: Thanh
--Ngày tạo: 12/5/2014
--Ngày cập nhật cuối: 25/02/2015
--Mục đích: lấy danh sách tất cả vật tư tồn trong 1 kho
--**********************************************************/
ALTER proc [dbo].[sp_T_GetVatTuTon]
@MaKho int
as
	begin
		select t.ID_MatHang, vt.VatTu, vt.MaKyHieu, t.SoTheKho, t.TangKeKhoang
		from tblVatTu vt, tblTon t
		where vt.ID_VatTu = t.ID_VatTu and t.ID_Kho = @MaKho
	end
GO
-- =============================================
-- Author:		Thanh
-- Create date: 23/5/2014
-- Sửa ngày: 25/02/2015
-- Description:	Kiểm tra số liệu
-- =============================================
ALTER PROCEDURE [dbo].[sp_T_KiemTraSoLieu]
	@maKho int
AS
BEGIN
	select t.ID_MatHang, vt.VatTu, vt.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	,sum(w.NhapKH) as pNhapKH, sum(w.NhapTH) as pNhapTH, sum(w.XuatKH) as pXuatKH, sum(w.XuatTH) as pXuatTH
	from
		(
			select ct.ID_MatHang, ct.SLKH as NhapKH, 0 as XuatKH, ct.SLTH as NhapTH, 0 as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='N'
			union all
			select ct.ID_MatHang, 0 as NhapKH, ct.SLKH as XuatKH, 0 as NhapTH, ct.SLTH as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='X'
		) as w, tblTon as t, tblVatTu as vt
	where w.ID_MatHang = t.ID_MatHang and t.ID_VatTu= vt.ID_VatTu and t.ID_Kho = @maKho
	group by  t.ID_MatHang, vt.VatTu, vt.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	having (sum(w.NhapKH)<> t.SLNhapKH) or (sum(w.NhapTH)<> t.SLNhapTH)
	or (sum(w.XuatKH)<> t.SLXuatKH) or (sum(w.XuatTH)<> t.SLXuatTH)
	or ((t.SLSS11+sum(w.NhapKH)-sum(w.XuatKH)) <> t.SLTonKH)
	or ((t.SLSS11+sum(w.NhapTH)-sum(w.XuatTH)) <> t.SLTonTH)
END
GO
-- =============================================
-- Author:		Thanh
-- Create date: 23/5/2014
-- Last update: 25/02/2015
-- Description:	Kiểm tra số liệu tất cả các kho
-- =============================================
ALTER PROCEDURE [dbo].[sp_T_KiemTraSoLieu_All]	
AS
BEGIN
	select t.ID_MatHang, vt.VatTu, vt.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	,sum(w.NhapKH) as pNhapKH, sum(w.NhapTH) as pNhapTH, sum(w.XuatKH) as pXuatKH, sum(w.XuatTH) as pXuatTH
	from
		(
			select ct.ID_MatHang, ct.SLKH as NhapKH, 0 as XuatKH, ct.SLTH as NhapTH, 0 as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='N'
			union all
			select ct.ID_MatHang, 0 as NhapKH, ct.SLKH as XuatKH, 0 as NhapTH, ct.SLTH as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='X'
		) as w, tblTon as t, tblVatTu as vt
	where w.ID_MatHang = t.ID_MatHang and t.ID_VatTu= vt.ID_VatTu 
	group by  t.ID_MatHang, vt.VatTu, vt.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	having (sum(w.NhapKH)<> sum(w.NhapTH)) or (sum(w.XuatKH)<> sum(w.XuatTH)) 
	or (sum(w.NhapKH)<> t.SLNhapKH) or (sum(w.NhapTH)<> t.SLNhapTH)
	or (sum(w.XuatKH)<> t.SLXuatKH) or (sum(w.XuatTH)<> t.SLXuatTH)
	or (t.SLNhapKH <> t.SLNhapTH) or (t.SLXuatKH<>t.SLXuatTH)
END
GO
--***************************************************************************
ALTER procedure [dbo].[sp_T_LayThongTinKiemKe]
	@dotKK int
as
begin
select kk.*, vt.VatTu, vt.MaKyHieu,dv.DVT, t.Gia
from tblKiemKe kk, tblTon t, tblVatTu vt, tblDVT dv
where kk.DotKiemKe = @dotKK and kk.ID_MatHang = t.ID_MatHang and t.ID_VatTu = vt.ID_VatTu and vt.ID_DVT = dv.ID_DVT
end
GO
--******************************************
--Noi dung: Lấy thông tin phiếu nhập xuất của mặt hàng
--Ngày tạo: 19/5/2014
--Ngày cập nhật: 25/02/2015
--Người tạo: Thanh
--******************************************/
ALTER proc [dbo].[sp_Thanh_TTNhapXuatMatHang]
@idMatHang int
as
	begin
		select p.NgayPhieu, p.SoPhieu as PhieuNhap,'' as PhieuXuat,ct.SLTH as SLNhap, 0 as SLXuat, ct.GhiChu
		from tblSLNX ct, tblDPNX p
		where ct.ID_DPNX= p.ID_DPNX and ct.ID_MatHang = @idMatHang
		and p.NX='N'
		union all
		select p.NgayPhieu,'' as PhieuNhap ,p.SoPhieu as PhieuXuat,0 as SLNhap, ct.SLTH as SLXuat,ct.GhiChu
		from tblSLNX ct, tblDPNX p
		where ct.ID_DPNX= p.ID_DPNX and ct.ID_MatHang = @idMatHang
		and p.NX='X'
	end